package com.ftd.DBConnection;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ftd.model.*;
import org.apache.http.client.fluent.Content;
import org.apache.http.client.fluent.Request;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by apple on 16/8/4.
 */
public class DBConnection {
    public static Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/HiBook?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        Connection connection = null;
        String name = "root";
        String password = "1";
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection(url, name, password);


        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return connection;

    }
    public static void insert(String phonenumber) {

        Connection connection = getConnection();

        String sql = "INSERT INTO user(phonenumber) VALUES(?)";

        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, phonenumber);

            preparedStatement.execute();
            preparedStatement.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public static void insert2(String username,String phonenumber_al,String sex,String phonenumber) {

        Connection connection = getConnection();

        String sql = "INSERT INTO user(username,phonenumber_al,sex) VALUES(?,?,?) WHERE phonenumber=\'"+phonenumber+"\'";

        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, phonenumber_al);
            preparedStatement.setString(3, sex);

            preparedStatement.execute();
            preparedStatement.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void insert10(String phonenumber) {

        Connection connection = getConnection();

        String sql = "INSERT INTO collectionTable(user) VALUES(?)";

        PreparedStatement preparedStatement = null;

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, phonenumber);

            preparedStatement.execute();
            preparedStatement.close();
            connection.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    public static List<Ebook> select1(String table) {



        String sql = "SELECT * FROM "+table;
        List<Ebook> userList = new ArrayList<Ebook>();
        PreparedStatement preparedStatement;

        try {
            preparedStatement = getConnection().prepareStatement(sql);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Ebook ebook = new Ebook();

                ebook.seteBookName(resultSet.getString("bookName"));
                ebook.seteBookReading(resultSet.getString("reading"));
                ebook.seteBookReview(resultSet.getString("review"));
                ebook.seteImageUrl(resultSet.getString("imageUrl"));
                ebook.setTag(resultSet.getString("tag"));
                ebook.setAuthor(resultSet.getString("author"));
                ebook.setBookUrl(resultSet.getString("bookUrl"));

                userList.add(ebook);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userList;

    }
    public static List<Index> select3(String table) {
        String sql = "SELECT * FROM "+table;
        List<Index> userList = new ArrayList<Index>();
        PreparedStatement preparedStatement;

        try {
            preparedStatement = getConnection().prepareStatement(sql);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Index index = new Index();
                index.setIbImageUrl(resultSet.getString("topImageUrl"));
                index.setItImageUrl(resultSet.getString("bottomImageUrl"));
                userList.add(index);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;

    }
    public static List<User> select4(String table) {
        String sql = "SELECT * FROM "+table;
        List<User> userList = new ArrayList<User>();
        PreparedStatement preparedStatement;

        try {
            preparedStatement = getConnection().prepareStatement(sql);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                User user = new User();
                user.setPhonenumber(resultSet.getString("phonenumber"));
                user.setIconUrl(resultSet.getString("iconUrl"));
                user.setName(resultSet.getString("name"));
                userList.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;

    }
    public static List<Mark> select2(String table) {



        String sql = "SELECT * FROM "+table;
        List<Mark> userList = new ArrayList<Mark>();
        PreparedStatement preparedStatement;

        try {
            preparedStatement = getConnection().prepareStatement(sql);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                Mark mark = new Mark();

                mark.setmCount(resultSet.getString("reading"));
                mark.setmImageUrl(resultSet.getString("imageUrl"));
                mark.setmTitle(resultSet.getString("title"));
                mark.setmType(resultSet.getString("type"));
                mark.setComment(resultSet.getString("comment"));
                mark.setTag(resultSet.getString("tag"));
                mark.setFilmUrl(resultSet.getString("filmUrl"));

                userList.add(mark);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userList;

    }
    public static int select5(String table,String phonenumber) {


        int value = 0;

        String sql = "SELECT tag FROM "+table+" WHERE user="+phonenumber;

        PreparedStatement preparedStatement;

        try {
            preparedStatement = getConnection().prepareStatement(sql);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                value = resultSet.getInt("tag");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return value;

    }
    public static ArrayList<String> select6(String table,String phonenumber) {



        ArrayList<String> list = new ArrayList<String>();

        String sql = "SELECT bookTag0,bookTag1,bookTag2,bookTag3,bookTag4,bookTag5,bookTag6 FROM "+table+" WHERE user=\'"+phonenumber+"\'";

        PreparedStatement preparedStatement;

        try {
            preparedStatement = getConnection().prepareStatement(sql);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                list.add(resultSet.getString("bookTag0"));
                list.add(resultSet.getString("bookTag1"));
                list.add(resultSet.getString("bookTag2"));
                list.add(resultSet.getString("bookTag3"));
                list.add(resultSet.getString("bookTag4"));
                list.add(resultSet.getString("bookTag5"));
                list.add(resultSet.getString("bookTag6"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;

    }
    public static void update(String phonenumber, String bookTag ) {

        int  n = select5("collectionTable",phonenumber);
        System.out.print("xxx"+n);

        String sql = "update collectionTable set bookTag"+n+"="+bookTag+" where user=\'"+phonenumber+"\'";
        n++;
        update2(n,phonenumber);

        System.out.println(sql);
        Connection conn = null;
        Statement st = null;
        try {
            conn = DBConnection.getConnection();
            st = conn.createStatement();
            int result = st.executeUpdate(sql);
            //处理结果
            if(result>0){
                System.out.println("操作成功");
            }else{
                System.out.println("操作失败");
            }
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void update2(int n ,String phonenumber) {


        String sql = "update collectionTable set tag="+n+" WHERE user=\'"+phonenumber+"\'";

        System.out.println(sql);
        Connection conn = null;
        Statement st = null;
        try {
            //从工具类中获取连接
            conn = DBConnection.getConnection();
            st = conn.createStatement();
            int result = st.executeUpdate(sql);
            //处理结果
            if(result>0){
                System.out.println("操作成功");
            }else{
                System.out.println("操作失败");
            }
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }


    }

    public static void update3(String username,String phonenumber_al,String sex,String phonenumber) {


        String sql = "update user set username=\'"+username+"\',phonenumber_al=\'"+phonenumber_al+"\',sex=\'"+sex+"\',name=\'"+username+"\' WHERE phonenumber="+phonenumber;

        System.out.println(sql);
        Connection conn = null;
        Statement st = null;
        try {
            //从工具类中获取连接
            conn = DBConnection.getConnection();
            st = conn.createStatement();
            int result = st.executeUpdate(sql);
            //处理结果
            if(result>0){
                System.out.println("操作成功");
            }else{
                System.out.println("操作失败");
            }
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }


    }
    public static void update4(String iconUrl,String phonenumber) {


        String sql = "update user set iconUrl=\'"+iconUrl+"\' WHERE phonenumber=\'"+phonenumber+"\'";

        System.out.println(sql);
        Connection conn = null;
        Statement st = null;
        try {
            //从工具类中获取连接
            conn = DBConnection.getConnection();
            st = conn.createStatement();
            int result = st.executeUpdate(sql);
            //处理结果
            if(result>0){
                System.out.println("操作成功");
            }else{
                System.out.println("操作失败");
            }
            st.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }


    }




    public static void main(String[] args) throws IOException {
//        update("15564140625","10");
//        System.out.print(select5("collectionTable","15564140625"));
//        update2(10);
//        update4("www.baidu.com","15564140625");x
        System.out.print(select6("collectionTable","15564140625"));
    }


}


